
const express = require("express");
const router = express.Router();
const utils = require("../../utils/index.js");
const pools = require("../../utils/pools.js");
const poolNoReturn = require("../../utils/poolNoReturn.js");

const multer  = require('multer');
const fs = require('fs');

// 设置存储配置
const storage = multer.diskStorage({
    destination: function (req, file, cb) {
      cb(null, 'uploads/') // 确保这个目录存在
    },
    filename: function (req, file, cb) {
      cb(null, req.body.title+ '-' + req.body.time + '.' + file.originalname.split('.').pop()) // 使用原始文件名和日期作为唯一标识
    }
  });
const upload = multer({ storage: storage });


//上传sqlite文件
router.post("/alarmUpload", upload.single('file'), async (req, res) => {
  const costNumber=req.body.costNumber;
  const equipmentCode=req.body.equipmentCode;
  const title=req.body.title;
    const time=req.body.time;
      const sqlite3 = require('sqlite3').verbose();
      const db = new sqlite3.Database('./uploads/'+title+ '-' + time + '.' +req.file.originalname.split('.').pop()); // 
      db.serialize(() => {
          // 这里可以写SQL语句来操作数据库，例如读取数据等。这里仅作为示例。
          db.each(`SELECT * FROM TB_PLCAlarm`, [], (err, row) => { // 替换为你的查询语句和表名
              if (err) {
                  res.status(500).send("Error executing query");
              } else {
                const sql = 'INSERT INTO alarm_metadata (cost_number,equipment_code,JIG_ID,ALARM_TYPE,ALARM_CODE,ALARM_CONTENT,ALARM_LEVEL,ALARM_OCCURED_TIME,INS_TIME,VALID_TAG)  VALUES (?,?,?,?,?,?,?,?,?,?)';
                pools({sql,val:[costNumber, equipmentCode,row.JIG_ID, row.ALARM_TYPE,row.ALARM_CODE, row.ALARM_CONTENT,row.ALARM_LEVEL,row.ALARM_OCCURED_TIME,row.INS_TIME,row.VALID_TAG],res,req,run:true});
              }
          });
      });
      res.send(utils.returnData({ data: {}}));
});

router.post("/productUpload", upload.single('file'), async (req, res) => {
    const costNumber=req.body.costNumber;
    const equipmentCode=req.body.equipmentCode;
    const title=req.body.title;
    const time=req.body.time;
      const sqlite3 = require('sqlite3').verbose();
      const db = new sqlite3.Database('./uploads/'+title+ '-' + time + '.' +req.file.originalname.split('.').pop()); // 
      db.serialize(() => {
          db.each(`SELECT * FROM TB_CellInfo`, [], (err, row) => { // 替换为你的查询语句和表名
              if (err) {
                  res.status(500).send("Error executing query");
              } else {
                if(row.TEST_START_TIME > '2025-01-01'){
                  const sql = 'INSERT INTO production_metadata (cost_number,equipment_code,JIG_TYPE,JIG_ID,JIG_CHANNEL_ID,TRAY_ID,CHANNEL_ID,CELL_ID,CHECK_RESULT,NG_REASON,DATA_PATH,TEST_START_TIME,TEST_COMPLETE_TIME,INS_TIME,END_VOLT,EDN_CAPT,END_ENGY,END_TEMP,END_PRESS)  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
                  pools({sql,val:[costNumber, equipmentCode,row.JIG_TYPE, row.JIG_ID,row.JIG_CHANNEL_ID, row.TRAY_ID,row.CHANNEL_ID,row.CELL_ID,row.CHECK_RESULT, row.NG_REASON,row.DATA_PATH, row.TEST_START_TIME,row.TEST_COMPLETE_TIME,row.INS_TIME, row.END_VOLT,row.EDN_CAPT,row.END_ENGY,row.END_TEMP,row.END_PRESS],res,req,run:true});
                }
                
              }
          });
      });
      res.send(utils.returnData({ data: {}}));
  });

router.post("/getAllDeviceByCustNumber", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT  id,device_uuid AS deviceUuid,device_code as deviceCode,device_name as deviceName,device_type as deviceType,cost_number as costNumber  FROM device  where   cost_number= '${obj.costNumber}'`;
    sql=utils.setLike(sql,"device_uuid",obj.title);
    let {total}=await utils.getSumBySql({sql,res,req});
    sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,res,req});
    res.send(utils.returnData({ data: result ,total}));
});


//获取设备的汇总数据
router.post("/getTotalDeviceData", async (req, res) => {
    let obj=req.body;
    let sql =`select * from equipment_statistics   WHERE cost_number = '${obj.costNumber}'`;
    let {result}=await pools({sql,val:[],res,req});
    res.send(utils.returnData({data: result}));
  });
  


  //初始化设备列表
  router.post("/createDeviceList", async (req, res) => {
	
    let obj=req.body;
    const num=obj.deviceNumber;
    let type=''
    switch (obj.deviceType) {
        case 1:
            type = 'HC'
          break
        case 2:
            type = 'FR'
          break
        case 3:
            type = 'DCR'
          break
        case 4:
            type = 'OCV'
          break
        case 5:
            type = 'LPM'
          break
        case 6:
            type = 'FX'
          break
          case 7:
            type = 'YQ'
          break
        case 8:
            type = 'HF'
          break
        default:
          break
      }
	for(let i=0; i<num; i++) {
		const  sql = `INSERT INTO device (device_uuid,device_code, device_name, device_type,cost_number) VALUES (?,'','',?,?) `;
		await pools({sql,val:[type+'-'+obj.costNumber.substring(0, 7)+'-'+(i+1).toString().padStart(4, '0'),obj.deviceType,obj.costNumber],res,req,run:true});
	}
	return res.send(utils.returnData({ data: '' }));
});


//获取设备的产量数据
router.post("/getProductByDevice", async (req, res) => {
    let obj=req.body;
    let sql = `SELECT
	id,
	JIG_ID,
	CHECK_RESULT,
	NG_REASON,
	TEST_START_TIME,
	TEST_COMPLETE_TIME,
	INS_TIME
FROM
	production_metadata  where  equipment_code= '${obj.equipmentCode}'`;
    let {total}=await utils.getSumBySql({sql,res,req});
    sql+=` ORDER BY INS_TIME DESC`;
    sql=utils.pageSize(sql,obj.pageNo,obj.pageSize);
    let {result}=await pools({sql,val:[],res,req});
    res.send(utils.returnData({data: result,total}));
});


module.exports = router;
